package models.outstock

import java.sql.Timestamp
import java.time.LocalDateTime
import java.time.format.DateTimeFormatter

import javax.inject.Inject
import play.api.Logging
import play.api.db.slick.{DatabaseConfigProvider, HasDatabaseConfigProvider}
import services.OutStockPostParameters
import slick.jdbc.JdbcProfile
import slick.lifted.ForeignKeyQuery
import slick.sql.SqlProfile.ColumnOption.SqlType

import scala.concurrent.duration.Duration
import scala.concurrent.{Await, ExecutionContext, Future}

/**
 * 销售发货汇总单ORM
 *
 * @param dbConfigProvider
 * @param executionContext
 */
class OutStockSumRepository @Inject()(
                                       protected val dbConfigProvider: DatabaseConfigProvider
                                     )(implicit executionContext: ExecutionContext)
  extends HasDatabaseConfigProvider[JdbcProfile] with Logging {

  import profile.api._

  private class OutStockSumTable(tag: Tag) extends Table[OutStockSum](tag, "OutStockSum".toUpperCase) {
    def id = column[Option[Int]]("id".toUpperCase, O.PrimaryKey)

    def ossFDate = column[Timestamp]("FDate".toUpperCase, SqlType("timestamp not null default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP"))

    def ossFGYBillNo = column[String]("FGYBillNo".toUpperCase)

    def ossFShopNumber = column[String]("FShopNumber".toUpperCase)

    def ossFStockNumber = column[String]("FStockNumber".toUpperCase)

    def ossSumInfo = column[Option[String]]("SumInfo".toUpperCase)

    def ossKDResultID = column[Option[String]]("KDResultID".toUpperCase)

    def ossResult = column[Option[String]]("Result".toUpperCase)

    def ossFEntity = column[Boolean]("FEntity".toUpperCase)

    def ossStatus = column[Int]("Stat".toUpperCase)

    def * = (
      id, ossFDate, ossFGYBillNo, ossFShopNumber, ossFStockNumber,
      ossSumInfo, ossKDResultID, ossResult, ossFEntity, ossStatus
      ).<>((OutStockSum.apply _).tupled, OutStockSum.unapply)
  }

  private class OutStockSumFEntityTable(tag: Tag) extends Table[OutStockSumFEntity](tag, "OutStockSumFEntity".toUpperCase) {
    def id = column[Option[Int]]("id".toUpperCase, O.PrimaryKey, O.AutoInc)

    def ossOutStockID = column[Option[Int]]("OutStockID_id".toUpperCase)

    def ossFAllAmount = column[Double]("FAllAmount".toUpperCase)

    def ossFAmount = column[Double]("FAmount".toUpperCase)

    def ossFDiscount = column[Double]("FDiscount".toUpperCase)

    def ossFFeeAmount = column[Double]("FFeeAmount".toUpperCase)

    def ossFIsFree = column[Boolean]("FIsFree".toUpperCase)

    def ossFMaterialNumber = column[String]("FMaterialNumber".toUpperCase)

    //def ossHashNo = column[Option[String]]("HashNo".toUpperCase)

    def ossFPrice = column[Double]("FPrice".toUpperCase)

    def ossFQty = column[Int]("FQty".toUpperCase)

    def ossFServiceAmount = column[Double]("FServiceAmount".toUpperCase)

    def * = (
      id, ossOutStockID, ossFAllAmount, ossFAmount, ossFDiscount, ossFFeeAmount,
      ossFIsFree, ossFMaterialNumber, ossFPrice, ossFQty, ossFServiceAmount
      ).<>((OutStockSumFEntity.apply _).tupled, OutStockSumFEntity.unapply)

    def outStockSum: ForeignKeyQuery[OutStockSumTable, OutStockSum] =
      foreignKey("OutStockID", ossOutStockID, TableQuery[OutStockSumTable])(_.id)
  }

  private val oss = TableQuery[OutStockSumTable]
  private val ossfe = TableQuery[OutStockSumFEntityTable]


  def listOss: Future[Seq[OutStockSum]] = db.run {
    oss.result
  }

  def list(p: OutStockPostParameters) = {
    val dtformat = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")
    val offset = p.limit * p.page
    val pOutStockSumId = if (p.p1.length > 4) p.p1.substring(4).toInt else 0
    val datestart = Timestamp.valueOf(LocalDateTime.parse(p.sdate.substring(0, 19)))
    val dateend = Timestamp.valueOf(LocalDateTime.parse(p.sdate.substring(22)))

    val f = oss
      .filterIf(p.p1.nonEmpty)(_.id.getOrElse(0) === pOutStockSumId)
      .filterIf(p.p1.isEmpty)(_.ossFDate >= datestart)
      .filterIf(p.p1.isEmpty)(_.ossFDate < dateend)
      .filterIf(p.status <= 2)(_.ossStatus === p.status)

    val res = f.sortBy(_.id).drop(offset).take(p.limit).map(f => f).result
    val total = f.length.result
    db.run(res).zip(db.run(total))
  }

  def ossInsert(rev: OutStockSum): Option[Int] = {
    val id = Await.result(db.run(oss.filter(_.id === rev.id).map(_.id).result), Duration.Inf)
    if (id.nonEmpty) id.head
    else Await.result(db.run((oss returning oss.map(_.id)) += rev), Duration.Inf)
  }

  def ossFEInsert(rev: OutStockSumFEntity) = {
    val id = Await.result(db.run(ossfe
      .filter(f =>
        f.ossOutStockID === rev.ossOutStockID && f.ossFMaterialNumber === rev.ossFMaterialNumber
      )
      .map(_.id).result), Duration.Inf)
    if (id.nonEmpty) id.head
    else Await.result(db.run((ossfe returning ossfe.map(_.id)) += rev), Duration.Inf)
  }

  def ossUpdateEnd(rev: OutStockSum) = db.run {
    if (rev.ossSumInfo.nonEmpty) {
      oss.filter(f => f.id === rev.id)
        .map(r =>
          (r.ossFEntity, r.ossSumInfo)
        ).update((true, rev.ossSumInfo))
    } else if (rev.ossResult.nonEmpty) {
      if (rev.ossStatus == 2 && rev.ossKDResultID.isEmpty) {
        oss.filter(f =>
          f.id === rev.id && f.ossStatus =!= 2
        ).map(r =>
          (r.ossStatus, r.ossResult)
        ).update((rev.ossStatus, rev.ossResult))
      } else {
        oss.filter(f => f.id === rev.id)
          .map(r =>
            (r.ossStatus, r.ossResult, r.ossKDResultID)
          ).update((rev.ossStatus, rev.ossResult, rev.ossKDResultID))
      }
    } else {
      oss.filter(f => f.id === rev.id)
        .map(r =>
          (r.ossStatus, r.ossResult)
        ).update((rev.ossStatus, rev.ossResult))
    }
  }

  def ossFind(datestart: Option[LocalDateTime] = None, dateend: Option[LocalDateTime] = None, status: Int = 2, sumId: Option[Int] = None) = {
    val f =
      if (sumId.nonEmpty) oss.filterOpt(sumId)(_.id === _)
      else {
        oss
          .filterOpt(datestart)(_.ossFDate >= Timestamp.valueOf(_))
          .filterOpt(dateend)(_.ossFDate < Timestamp.valueOf(_))
          .filterIf(status < 2)(_.ossStatus === status)
          .filterIf(status == 2)(_.ossStatus =!= status)
          .filter(_.ossFEntity === true)
      }
    db.run(f.map(f => f).result)
  }

  def ossFeFind(ossId: Int) = db.run {
    ossfe.filter(_.ossOutStockID === ossId).map(f => f).result
  }

  def ossSendFind = db.run(oss.filter(_.ossStatus =!= 2).map(f => f).result)

  def ossSendFind11111(datestart: Option[LocalDateTime] = None, dateend: Option[LocalDateTime] = None, status: Int = 2, sumId: Option[Int] = None) = {

    val joinQuery = for {
      s <- (oss.filterOpt(sumId)(_.id === _)
        .filterOpt(datestart)(_.ossFDate >= Timestamp.valueOf(_))
        .filterOpt(dateend)(_.ossFDate < Timestamp.valueOf(_))
        .filterIf(status < 2)(_.ossStatus === status)
        .filterIf(status == 2)(_.ossStatus =!= status))
      f <- ossfe if s.id === f.ossOutStockID
    } yield (s, f)
    db.run(joinQuery.result).map(println)
  }

}
